package data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



public class Connect {

	public Boolean isAdmin;
	public ArrayList<User> arrUser;
	public boolean isAuthenticated;


	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	String connectionUrl = "jdbc:mysql://localhost:3306/atm";
	String connectionUser = "dataguy";
	String connectionPassword = "QjcUayHA3axeGLns";

	public Connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(connectionUrl, connectionUser,
					connectionPassword);
			stmt = conn.createStatement();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public ArrayList<User> searchUsers(String query) {

		ArrayList<User> arrUsers;
		arrUsers = new ArrayList<User>();
		try {
			// new com.mysql.jdbc.Driver();
			rs = stmt
					.executeQuery("SELECT * FROM users where UserFirstName like '%"
							+ query
							+ "%' or UserLastName like '%"
							+ query
							+ "%'");
			while (rs.next()) {
				arrUsers.add(new User(rs.getString("UserAvatar"), rs
						.getString("UserFirstName"), rs
						.getString("UserLastName")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return arrUsers;
	}

	public boolean authenticateUser(String username, String password) {
		isAuthenticated = false;
		isAdmin = false;

		String query = "SELECT * FROM users where UserPrimaryKey = '"
				+ username + "' and UserPassword = '" + password + "'";

		try {
			// new com.mysql.jdbc.Driver();
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				isAuthenticated = true;

				// getSingleUser(rs.getInt("UserID"));

				if (rs.getInt("UserType") == 1) {
					isAdmin = true;
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return isAuthenticated;
	}

	public ArrayList<User> getUsers(int tempUsersID) {

		ArrayList<User> arrUsers;
		arrUsers = new ArrayList<User>();
		try {
			// new com.mysql.jdbc.Driver();
			rs = stmt.executeQuery("SELECT * FROM users");
			while (rs.next()) {
				arrUsers.add(new User(rs.getString("UserAvatar"), rs
						.getString("UserFirstName"), rs
						.getString("UserLastName")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return arrUsers;
	}

	public ArrayList<User> getSingleUser(int SingleUserID) {

		arrUser = new ArrayList<User>();
		try {
			// new com.mysql.jdbc.Driver();
			rs = stmt.executeQuery("SELECT * FROM users WHERE UserID = '"
					+ SingleUserID + "'");
			while (rs.next()) {
				arrUser.add(new User(rs.getInt("UserID"), rs
						.getString("UserPassword"), rs
						.getString("UserFirstname"), rs
						.getString("UserLastname"), rs.getString("UserEmail"),
						rs.getInt("UserAccountID"), rs.getInt("UserType"), rs
								.getInt("UserEnrollYear"), rs
								.getString("UserPrimaryKey"), rs
								.getInt("UserPhone"), rs
								.getString("UserStatus"), rs
								.getString("UserAvatar")));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return arrUser;
	}
	
	
	
	public User getUser(String userName) {

		User user = null;
		try {
			conn = DriverManager.getConnection(connectionUrl, connectionUser,
					connectionPassword);
			stmt = conn.createStatement();
			// new com.mysql.jdbc.Driver();
			rs = stmt.executeQuery("SELECT * FROM users WHERE UserPrimaryKey = '"
					+ userName + "'");
			while (rs.next()) {
				user = new User(rs.getInt("UserID"), rs
						.getString("UserPassword"), rs
						.getString("UserFirstname"), rs
						.getString("UserLastname"), rs.getString("UserEmail"),
						rs.getInt("UserAccountID"), rs.getInt("UserType"), rs
								.getInt("UserEnrollYear"), rs
								.getString("UserPrimaryKey"), rs
								.getInt("UserPhone"), rs
								.getString("UserStatus"), rs
								.getString("UserAvatar"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}

	public ArrayList<Activity> getActivities() {

		ArrayList<Activity> arrActivities;
		arrActivities = new ArrayList<Activity>();
		try {
			// new com.mysql.jdbc.Driver();
			
			rs = stmt
					.executeQuery("select activities.*, users.UserFirstname, users.UserLastName from activities left join users on activities.ActivityUserID = users.UserID order by ActivityDate desc");
			while (rs.next()) {
				int ActivityID = rs.getInt("ActivityID");
				int UserID = rs.getInt("ActivityUserID");
				int Amount = rs.getInt("ActivityAmount");
				int AmountDKK = rs.getInt("ActivityAmountDKK");
				String Description = rs.getString("ActivityDescription");
				String Date = rs.getString("ActivityDate");
				String RecipientID = rs.getString("ActivityRecipientID");
				String Type = rs.getString("ActivityType");
				String UserFirstname = rs.getString("UserFirstname");
				String UserLastname = rs.getString("UserLastname");
				arrActivities.add(new Activity(ActivityID, UserID, Amount,
						AmountDKK, Description, Date, RecipientID, Type));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return arrActivities;
	}
}